﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace _1stVATCWin
{
    public partial class HopDong : Form
    {
        public HopDong()
        {
            InitializeComponent();
        }

        private void HopDong_Load(object sender, EventArgs e)
        {       
            load_DSHD();
        }

        private void load_DSHD()
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select IDhopdong,ngayhopdong,HOPDONG.IDNCC,tenNCC,dieukhoan,IDnhanvien,tonggianhap,xacnhan from HOPDONG join NHACUNGCAP on HOPDONG.IDNCC = NHACUNGCAP.IDNCC";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table HopDong");
            DataTable dt = ds.Tables["Table HopDong"];

            DSHDCT.AutoGenerateColumns = false;
            DSHD.DataSource = ds.Tables["Table HopDong"].DefaultView;
        }

        private void load_DSHDCT()
        {
            int i = DSHD.SelectedRows[0].Index;
            string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
            string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();

            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select IDhopdong,ngayhopdong,SANPHAM.IDsanpham,tensanpham,tenloai,slnhap,dongianhap" +
                           " from HOPDONG_CT join SANPHAM on HOPDONG_CT.IDsanpham = SANPHAM.IDsanpham join LOAISANPHAM on SANPHAM.IDloai = LOAISANPHAM.IDloai" +
                           " where IDhopdong = @id and ngayhopdong = @ngay";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@ngay", date);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table HopDongCT");
            DataTable dt = ds.Tables["Table HopDongCT"];

            DSHDCT.AutoGenerateColumns = false;
            DSHDCT.DataSource = ds.Tables["Table HopDongCT"].DefaultView;
        }

        private void load_IDNCC()
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from NHACUNGCAP";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table NCC");
            DataTable dt = ds.Tables["Table NCC"];

            idncc.DisplayMember = "tenNCC";
            idncc.ValueMember = "IDNCC";
            idncc.DataSource = ds.Tables["Table NCC"].DefaultView;
        }

        private void load_IDsanpham(string loai)
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from SANPHAM where IDloai = @loai";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@loai", loai);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table SanPham");
            DataTable dt = ds.Tables["Table SanPham"];

            hd_tsp.DisplayMember = "tensanpham";
            hd_tsp.ValueMember = "IDsanpham";
            hd_tsp.DataSource = ds.Tables["Table SanPham"];
        }

        private void load_IDloai()
        {
            int i = DSHD.SelectedRows[0].Index;
            string ncc = DSHD.Rows[i].Cells["NCCID"].Value.ToString();

            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select NCC_CT.IDloai,tenloai from NCC_CT join LOAISANPHAM on NCC_CT.IDloai = LOAISANPHAM.IDloai where IDNCC = @id";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@id", ncc);
            
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table LoaiSanPham");
            DataTable dt = ds.Tables["Table LoaiSanPham"];

            hd_lsp.DisplayMember = "tenloai";
            hd_lsp.ValueMember = "IDloai";
            hd_lsp.DataSource = ds.Tables["Table LoaiSanPham"];
        }

        private void load_TongGiaTriNhap()
        {
            if (DSHDCT.Rows.Count == 0)
            {
                tongNhap.Text = "0";
            }
            else
            {
                int i = DSHD.SelectedRows[0].Index;
                string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
                string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();

                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select SUM(slnhap*dongianhap) as tongdgn from HOPDONG_CT where IDhopdong = @id and ngayhopdong = @date";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@date", date);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                    tongNhap.Text = r["tongdgn"].ToString();
                r.Close();
                conn.Close();
            }
        }

        private void update_TongGiaTriNhap()
        {
            load_TongGiaTriNhap();

            int i = DSHD.SelectedRows[0].Index;
            string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
            string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();

            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            //string connectionString = @"Data Source=FOX-PC\SQLEXPRESS;Initial Catalog=Project;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "update HOPDONG set tonggianhap = @tgn where IDhopdong = @id and ngayhopdong = @ngay";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@ngay", date);
            cmd.Parameters.AddWithValue("@tgn", tongNhap.Text);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            
        }

        private void update_SoLuongTon()
        {
            int i = DSHD.SelectedRows[0].Index;
            string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
            string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();
            string idncc = DSHD.Rows[i].Cells["NCCID"].Value.ToString();

            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from HOPDONG_CT where IDhopdong = @id and ngayhopdong = @ngay";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@ngay", date);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table A");
            DataTable dt = ds.Tables["Table A"];

            foreach (DataRow r in dt.Rows)
            {
                table_SANPHAM_CT(r["IDsanpham"].ToString(), idncc, (int)r["slnhap"]);
                cmd.CommandText = "update SANPHAM set soluongton = soluongton + " + (int)r["slnhap"] + " where IDsanpham = '" + r["IDsanpham"].ToString() + "'";
                cmd.ExecuteNonQuery();
            }
            conn.Close();
        }

        private void table_SANPHAM_CT(string idsp, string idncc,int sl)
        {
            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from SANPHAM_CT where IDsanpham = @idsp and IDNCC = @idncc";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@idsp", idsp);
            cmd.Parameters.AddWithValue("@idncc", idncc);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Table SANPHAM_CT");
            DataTable dt = ds.Tables["Table SANPHAM_CT"];
           
            if (dt.Rows.Count != 0)
            {
                cmd.CommandText = "update SANPHAM_CT set soluong = soluong + @sl where IDsanpham = @idsp and IDNCC = @idncc";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@idsp", idsp);
                cmd.Parameters.AddWithValue("@idncc", idncc);
                cmd.Parameters.AddWithValue("@sl", sl);
                cmd.ExecuteNonQuery();
            }
            else
            {
                cmd.CommandText = "insert into SANPHAM_CT(IDsanpham,IDNCC,soluong) values(@idsp,@idncc,@sl)";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@idsp", idsp);
                cmd.Parameters.AddWithValue("@idncc", idncc);
                cmd.Parameters.AddWithValue("@sl", sl);
                cmd.ExecuteNonQuery();
            }
            conn.Close();
        }

        private void hd_lsp_SelectedIndexChanged(object sender, EventArgs e)
        {
            load_IDsanpham(hd_lsp.SelectedValue.ToString());
        }

        private void Auto_Load_1()
        {            
            int i = DSHD.SelectedRows[0].Index;
            string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
            string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();

            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select * from HOPDONG where IDhopdong = @id and ngayhopdong = @ngay";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@ngay", date);

            conn.Open();
            SqlDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                ngayhd.Value = (DateTime)r["ngayhopdong"];
                dkhd.Text = r["dieukhoan"].ToString();
                idncc.SelectedValue = r["IDNCC"].ToString();
            }
            r.Close();
            conn.Close();
        }

        private void Auto_Load_2()
        {
            int i = DSHDCT.SelectedRows[0].Index;
            string id = DSHDCT.Rows[i].Cells["IDsanpham"].Value.ToString();

            Config file = new Config(Application.StartupPath + "\\config.ini");
            string connectionString = file.ReadValue("server", "conn");
            SqlConnection conn = new SqlConnection(connectionString);
            string sql = "select IDhopdong,ngayhopdong,SANPHAM.IDsanpham,LOAISANPHAM.IDloai,slnhap,dongianhap" +
                           " from HOPDONG_CT join SANPHAM on HOPDONG_CT.IDsanpham = SANPHAM.IDsanpham join LOAISANPHAM on SANPHAM.IDloai = LOAISANPHAM.IDloai" +
                           " where HOPDONG_CT.IDsanpham = @id";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@id", id);

            conn.Open();
            SqlDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {                
                hd_lsp.SelectedValue = r["IDloai"].ToString();
                hd_tsp.SelectedValue = r["IDsanpham"].ToString();
                hd_sln.Text = r["slnhap"].ToString();
                hd_dgn.Text = r["dongianhap"].ToString();
            }
            r.Close();
            conn.Close();
        }

        private void DSHD_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            load_IDNCC();
            Auto_Load_1();            
            load_DSHDCT();            
            load_TongGiaTriNhap();            

            hd_tsp.Text = "";
            hd_lsp.Text = "";
            hd_sln.Text = "";
            hd_dgn.Text = "";
        }

        private void DSHDCT_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            load_IDloai();
            load_IDsanpham(hd_lsp.SelectedValue.ToString());
            Auto_Load_2();
        }

        private void Add_HD_Click(object sender, EventArgs e)
        {
            HD_ADD f = new HD_ADD();
            if (f.ShowDialog() == DialogResult.OK)
            {
                load_DSHD();
            }
        }

        private void Upd_HD_Click(object sender, EventArgs e)
        {
            if (DSHD.Rows.Count == 0)
                MessageBox.Show("Không có dữ liệu trong danh sách hợp đồng", "Thông Báo", MessageBoxButtons.OK);
            else
            {
                int i = DSHD.SelectedRows[0].Index;
                string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
                string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();

                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                //string connectionString = @"Data Source=FOX-PC\SQLEXPRESS;Initial Catalog=Project;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "update HOPDONG set IDNCC = @idncc,dieukhoan = @dk where IDhopdong = @idhd and ngayhopdong = @ngayhd";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@idhd", id);
                cmd.Parameters.AddWithValue("@ngayhd", date);
                cmd.Parameters.AddWithValue("@idncc", idncc.SelectedValue);
                cmd.Parameters.AddWithValue("@dk", dkhd.Text);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                load_DSHD();
            }
        }

        private void Del_HD_Click(object sender, EventArgs e)
        {
            if (DSHD.Rows.Count == 0)
                MessageBox.Show("Không có dữ liệu trong danh sách hợp đồng", "Thông Báo", MessageBoxButtons.OK);
            else            
            if ((MessageBox.Show("Bạn có muốn xóa dữ liệu không?", "Thông Báo", MessageBoxButtons.OKCancel)) == DialogResult.OK)
            {               
                int i = DSHD.SelectedRows[0].Index;
                string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
                string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();

                //xóa tất cả dữ liệu sản phẩm của hợp đồng trong bảng DSHDCT
                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                //string connectionString = @"Data Source=FOX-PC\SQLEXPRESS;Initial Catalog=Project;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "delete from HOPDONG_CT where IDhopdong = @idhd and ngayhopdong = @ngayhd";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@idhd", id);
                cmd.Parameters.AddWithValue("@ngayhd", date);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                //xóa dữ liệu trong bảng DSHD
                cmd.Parameters.Clear();
                cmd.CommandText = "delete from HOPDONG where IDhopdong = @idhd and ngayhopdong = @ngayhd";
                cmd.Parameters.AddWithValue("@idhd", id);
                cmd.Parameters.AddWithValue("@ngayhd", date);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                load_DSHDCT();
                DSHD.Rows.RemoveAt(i);
                load_DSHD();

                idncc.Text = "";
                dkhd.Text = "";
                tongNhap.Text = "";
            }
        }

        private void Add_SP_Click(object sender, EventArgs e)
        {
            if (DSHD.Rows.Count == 0)
                MessageBox.Show("Không có dữ liệu trong danh sách hợp đồng. Bạn phải thêm hợp đồng trước khi thêm sản phẩm", "Thông Báo", MessageBoxButtons.OK);
            else
            {
                int i = DSHD.SelectedRows[0].Index;
                string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
                string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();
                string idncc = DSHD.Rows[i].Cells["NCCID"].Value.ToString();
                HD_ADD_SP f = new HD_ADD_SP(id, date, idncc);
                if (f.ShowDialog() == DialogResult.OK)
                {
                    load_DSHDCT();
                    update_TongGiaTriNhap();
                }
            }
        }

        private void Upd_SP_Click(object sender, EventArgs e)
        {
            if (DSHDCT.Rows.Count == 0)
                MessageBox.Show("Không có dữ liệu trong danh sách hợp đồng chi tiết", "Thông Báo", MessageBoxButtons.OK);
            else
            {
                int i = DSHD.SelectedRows[0].Index;
                string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
                string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();
                int j = DSHDCT.SelectedRows[0].Index;
                string idsp = DSHDCT.Rows[j].Cells["IDsanpham"].Value.ToString();

                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                //string connectionString = @"Data Source=FOX-PC\SQLEXPRESS;Initial Catalog=Project;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "update HOPDONG_CT set slnhap = @slnhd, dongianhap = @dgnhd where IDhopdong = @idhd and ngayhopdong = @ngayhd and IDsanpham = @id";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@idhd", id);
                cmd.Parameters.AddWithValue("@ngayhd", date);
                cmd.Parameters.AddWithValue("@id", idsp);
                cmd.Parameters.AddWithValue("@slnhd", hd_sln.Text);
                cmd.Parameters.AddWithValue("@dgnhd", hd_dgn.Text);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                load_DSHDCT();
                update_TongGiaTriNhap();
            }
        }

        private void Del_SP_Click(object sender, EventArgs e)
        {
            if (DSHDCT.Rows.Count == 0)
                MessageBox.Show("Không có dữ liệu trong danh sách hợp đồng chi tiết", "Thông Báo", MessageBoxButtons.OK);
            else
            {
                if ((MessageBox.Show("Bạn có muốn xóa dữ liệu không?", "Thông Báo", MessageBoxButtons.OKCancel)) == DialogResult.OK)
                {
                    int i = DSHD.SelectedRows[0].Index;
                    string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
                    string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();
                    int j = DSHDCT.SelectedRows[0].Index;
                    string idsp = DSHDCT.Rows[j].Cells["IDsanpham"].Value.ToString();

                    Config file = new Config(Application.StartupPath + "\\config.ini");
                    string connectionString = file.ReadValue("server", "conn");
                    //string connectionString = @"Data Source=FOX-PC\SQLEXPRESS;Initial Catalog=Project;Integrated Security=True";
                    SqlConnection conn = new SqlConnection(connectionString);
                    string sql = "delete from HOPDONG_CT where IDhopdong = @idhd and ngayhopdong = @ngayhd and IDsanpham = @id";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@idhd", id);
                    cmd.Parameters.AddWithValue("@ngayhd", date);
                    cmd.Parameters.AddWithValue("@id", idsp);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    DSHDCT.Rows.RemoveAt(j);
                    update_TongGiaTriNhap();
                    hd_tsp.Text = "";
                    hd_lsp.Text = "";
                    hd_sln.Text = "";
                    hd_dgn.Text = "";
                    load_DSHDCT();
                }
            }
        }

        private void Comfig_Click(object sender, EventArgs e)
        {
            if (DSHD.Rows.Count == 0)
                MessageBox.Show("Không có dữ liệu trong danh sách hợp đồng", "Thông Báo", MessageBoxButtons.OK);
            else
            {
                int i = DSHD.SelectedRows[0].Index;
                string id = DSHD.Rows[i].Cells["IDhopdong"].Value.ToString();
                string date = DSHD.Rows[i].Cells["ngayhopdong"].Value.ToString();


                Config file = new Config(Application.StartupPath + "\\config.ini");
                string connectionString = file.ReadValue("server", "conn");
                SqlConnection conn = new SqlConnection(connectionString);
                string sql = "select * from HOPDONG where IDhopdong = @id and ngayhopdong = @ngay and xacnhan = N'Đã Xác Nhận'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@ngay", date);

                conn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                if (r.Read())
                {
                    MessageBox.Show("Hợp đồng này đã được xác nhận", "Thông Báo", MessageBoxButtons.OK);
                    r.Close();
                }
                else
                {
                    r.Close();
                    cmd.Parameters.Clear();
                    cmd.CommandText = "update HOPDONG set xacnhan = N'Đã Xác Nhận' where IDhopdong = @id and ngayhopdong = @ngay";
                    cmd.Parameters.AddWithValue("@id", id);
                    cmd.Parameters.AddWithValue("@ngay", date);
                    cmd.ExecuteNonQuery();
                    update_SoLuongTon();
                    load_DSHD();
                    load_DSHDCT();
                }
                conn.Close();
            }
        }


    }
}
